using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace CloudApiServer
{
    /// <summary>
    /// Mysql帮助类
    /// </summary>
    public static class MySqlHelper
    {
        /// <summary>
        /// 执行为非查询语句 插入、更新、删除等
        /// </summary>
        /// <param name="connectionString">mysql链接字符串</param>
        /// <param name="query">SQL 查询语句，通常是 INSERT、UPDATE 或 DELETE 语句</param>
        /// <param name="parameters">用于传递 SQL 查询语句中的参数，避免 SQL 注入攻击</param>
        /// <returns>收到影响的行数</returns>
        /// <exception cref="ArgumentNullException">mysql执行异常</exception>
        public static async Task<int> ExecuteNonQueryAsync(string connectionString, string query, IEnumerable<MySqlParameter> parameters = null)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                throw new ArgumentNullException(nameof(connectionString));
            }

            if (string.IsNullOrEmpty(query))
            {
                throw new ArgumentNullException(nameof(query));
            }

            try
            {
                using (var connection = new MySqlConnection(connectionString))
                {
                    await connection.OpenAsync();
                    using (var transaction = connection.BeginTransaction())
                    {
                        using (var command = new MySqlCommand(query, connection, transaction))
                        {
                            if (parameters != null)
                            {
                                command.Parameters.AddRange(parameters.ToArray());
                            }

                            LogHelper.Info($"[MySqlHelper.ExecuteNonQueryAsync]:{query}, Parameters:{FormatParameters(parameters)}");
                            int result = await command.ExecuteNonQueryAsync();
                            transaction.Commit();
                            return result;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error($"[MySqlHelper.ExecuteNonQueryAsync] Error: {ex.Message}, Query: {query}, Parameters:{FormatParameters(parameters)}");
                throw;
            }
        }

        /// <summary>
        /// 执行查询语句，返回DataTable
        /// </summary>
        /// <param name="connectionString">mysql链接字符串</param>
        /// <param name="query">sql查询语句</param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentNullException"></exception>
        public static async Task<DataTable> ExecuteQueryAsync(string connectionString, string query, IEnumerable<MySqlParameter> parameters = null)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                throw new ArgumentNullException(nameof(connectionString));
            }

            if (string.IsNullOrEmpty(query))
            {
                throw new ArgumentNullException(nameof(query));
            }

            try
            {
                using (var connection = new MySqlConnection(connectionString))
                {
                    await connection.OpenAsync();
                    using (var command = new MySqlCommand(query, connection))
                    {
                        if (parameters != null)
                        {
                            command.Parameters.AddRange(parameters.ToArray());
                        }

                        LogHelper.Info($"[MySqlHelper.ExecuteQueryAsync]:{query}, Parameters:{FormatParameters(parameters)}");
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            var dataTable = new DataTable();
                            dataTable.Load(reader);
                            return dataTable;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error($"[MySqlHelper.ExecuteQueryAsync] Error: {ex.Message}, Query: {query}, Parameters:{FormatParameters(parameters)}");
                throw;
            }
        }

        /// <summary>
        /// 执行查询语句，返回指定类型
        /// </summary>
        /// <typeparam name="T">指定类型</typeparam>
        /// <param name="connectionString">mysql链接字符串</param>
        /// <param name="query">sql查询语句</param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        /// <exception cref="ArgumentNullException"></exception>
        public static async Task<T> ExecuteScalarAsync<T>(string connectionString, string query, IEnumerable<MySqlParameter> parameters = null)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                throw new ArgumentNullException(nameof(connectionString));
            }

            if (string.IsNullOrEmpty(query))
            {
                throw new ArgumentNullException(nameof(query));
            }

            try
            {
                using (var connection = new MySqlConnection(connectionString))
                {
                    await connection.OpenAsync();
                    using (var command = new MySqlCommand(query, connection))
                    {
                        if (parameters != null)
                        {
                            command.Parameters.AddRange(parameters.ToArray());
                        }

                        var result = await command.ExecuteScalarAsync();
                        if (result == null || !typeof(T).IsAssignableFrom(result.GetType()))
                        {
                            return default(T);
                        }

                        LogHelper.Info($"[MySqlHelper.ExecuteScalarAsync]:{query}, Parameters:{FormatParameters(parameters)}");
                        return (T)Convert.ChangeType(result, typeof(T));
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error($"[MySqlHelper.ExecuteScalarAsync] Error: {ex.Message}, Query: {query}, Parameters:{FormatParameters(parameters)}");
                throw;
            }
        }

        /// <summary>
        /// 开始事务
        /// </summary>
        /// <param name="connectionString">mysql链接字符串</param>
        /// <returns>MySqlTransaction 对象</returns>
        public static async Task<MySqlTransaction> BeginTransactionAsync(string connectionString)
        {
            if (string.IsNullOrEmpty(connectionString))
            {
                throw new ArgumentNullException(nameof(connectionString));
            }

            var connection = new MySqlConnection(connectionString);
            await connection.OpenAsync();
            return connection.BeginTransaction();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        /// <param name="transaction">MySqlTransaction 对象</param>
        public static void CommitTransaction(MySqlTransaction transaction)
        {
            if (transaction == null)
            {
                throw new ArgumentNullException(nameof(transaction));
            }

            transaction.Commit();
            transaction.Connection.Close();
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        /// <param name="transaction">MySqlTransaction 对象</param>
        public static void RollbackTransaction(MySqlTransaction transaction)
        {
            if (transaction == null)
            {
                throw new ArgumentNullException(nameof(transaction));
            }

            transaction.Rollback();
            transaction.Connection.Close();
        }

        /// <summary>
        /// 格式化参数日志
        /// </summary>
        private static string FormatParameters(IEnumerable<MySqlParameter> parameters)
        {
            if (parameters == null || !parameters.Any())
            {
                return "No Parameters";
            }

            return string.Join(", ", parameters.Select(p => $"{p.ParameterName}={p.Value}"));
        }
    }
}
